﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Web;
using StackExchange.DataExplorer.Helpers;
using Dapper;
using System.Linq;
using StackExchange.DataExplorer;

namespace StackExchange.DataExplorer.Models
{
    public class Site
    {
        public int Id { get; set; }
        public string TinyName { get; set; }
        public string  Name { get; set; }
        public string LongName { get; set; }
        public string Url { get; set; }
        public string ImageUrl { get; set; }
        public string IconUrl { get; set; }
        public string DatabaseName { get; set; }
        public string Tagline { get; set; }
        public string TagCss { get; set; }
        public int? TotalQuestions { get; set; }
        public int? TotalAnswers { get; set; }
        public int? TotalUsers { get; set; }
        public int? TotalComments { get; set; }
        public int? TotalTags { get; set; }
        public DateTime? LastPost { get; set; }
        public string ImageBackgroundColor { get; set; }
        public string ConnectionStringOverride { get; set; }
        public int? ParentId { get; set; }
        // above props are columns on dbo.Sites

        private Site relatedSite;
        private bool checkedRelatedSite = false;

        public Site RelatedSite
        {
            get
            {
                if (!checkedRelatedSite && relatedSite == null)
                {
                    if (ParentId == null)
                    {
                        relatedSite = Current.DB.Query<Site>("SELECT * FROM Sites WHERE ParentId = @id", new { Id }).FirstOrDefault();
                    }
                    else
                    {
                        relatedSite = Current.DB.Query<Site>("SELECT * FROM Sites WHERE Id = @parentId", new { parentId = ParentId.Value }).FirstOrDefault();
                    }

                    // We could just query for the related site when getting this site and avoid the need
                    // for this variable, but we don't always need related site...so I'm not sure what's
                    // "less silly"...
                    checkedRelatedSite = true;
                }

                return relatedSite;
            }
            private set {}
        }

        public string ConnectionString
        {
            get { return UseConnectionStringOverride ? ConnectionStringOverride : ConnectionStringWebConfig; }
        }

        private string ConnectionProviderName
        {
            get
            {
                return ConnectionStringSettings.ProviderName.HasValue() ? ConnectionStringSettings.ProviderName : "System.Data.SqlClient";
            }
        }

        private string ConnectionStringWebConfig
        {
            get
            {
                return ConnectionStringSettings.ConnectionString.Replace("!!DB!!", DatabaseName);
            }
        }

        private ConnectionStringSettings ConnectionStringSettings
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["ReaderConnection"];
            }
        }

        private bool UseConnectionStringOverride
        {
            get { return ConnectionStringOverride.HasValue(); }
        }

        public string ImageCss
        {
            get
            {
                if (ImageBackgroundColor != null)
                {
                    return "background-color: #" + ImageBackgroundColor; 
                }
                return "";
            }
        }

        public string ODataEndpoint
        {
            get { return "/" + Name.ToLower() + "/atom"; }
        }

        public SqlConnection GetConnection(int maxPoolSize)
        {
            // TODO: do we even need this method any longer? are we still supporting about odata?
            var cs = ConnectionString + (UseConnectionStringOverride ? "" : string.Format("Max Pool Size={0};",maxPoolSize));
            return new SqlConnection(cs);
        }

        public DataExplorerSite GetOpenConnection(int timeout = 30, bool forceUncommitted = false)
        {
            ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["AppConnection"];
            DbConnection cnn = DbProviderFactories.GetFactory(ConnectionProviderName).CreateConnection();

            cnn.ConnectionString = ConnectionString;
            cnn.Open();

            var db = DataExplorerSite.Init(cnn, ConnectionProviderName, timeout);

            if (AppSettings.FetchDataInReadUncommitted || forceUncommitted)
            {
                var uncommitted = db.Dialect.GetQuery("Site.ReadUncommitted", null);

                if (uncommitted.HasValue())
                {
                    db.Execute(uncommitted);
                }
            }

            return db;
        }

        public bool SharesUsers(Site site)
        {
            var shares = false;

            if (this.Url.StartsWith("http://meta.") && this.Url != "http://meta.stackoverflow.com")
            {
                shares = this.Url.Substring("http://meta.".Length) == site.Url.Substring("http://".Length);
            }
            else if (site.Url.StartsWith("http://meta.") && site.Url != "http://meta.stackoverflow.com")
            {
                shares = site.Url.Substring("http://meta.".Length) == this.Url.Substring("http://".Length);
            }

            return shares;
        }

        public static IEnumerable<Site> GetSites()
        {
            // Could/should probably just cache this somewhere
            return Current.DB.Query<Site>(@"SELECT s.*
FROM
    Sites s
LEFT JOIN
    Sites m
ON
    REPLACE(s.Url, 'http://meta.', 'http://') = m.Url
ORDER BY
    isnull(m.Name,s.Name) asc,
    CHARINDEX('http://meta.', s.Url)"
            );
        }

        public void UpdateStats()
        {
            using (var db = GetOpenConnection(300))
            {
                TotalQuestions = db.Query<int>("Site.TotalQuestions", "SELECT COUNT(*) FROM Posts WHERE ParentId IS NULL").First();
                TotalAnswers = db.Query<int>("Site.TotalAnswers", "SELECT COUNT(*) FROM Posts WHERE ParentId IS NOT NULL").First();
                TotalComments = db.Query<int>("Site.TotalComments", "SELECT COUNT(*) FROM Comments").First();
                LastPost = db.Query<DateTime>("Site.LastPost", "SELECT MAX(CreationDate) FROM Posts").First();
                TotalUsers = db.Query<int>("Site.TotalUsers", "SELECT COUNT(*) FROM Users").First();
                TotalTags = db.Query<int>("Site.TotalTags", "SELECT COUNT(*) FROM Tags").First();
            }

            Current.DB.Sites.Update(Id, new {TotalQuestions, TotalAnswers, TotalComments, LastPost, TotalUsers, TotalTags });
        }

        public int? GuessUserId(User user)
        {
            if (user.IsAnonymous || !AppSettings.GuessUserId) return null;

            string cacheKey = "UserIdForSite" + Id + "_" + user.Id;

            var currentId = HttpContext.Current.Session[cacheKey] as int?;
            if (currentId == null)
            {
                int? id = FindUserId(user);
                if (id != null)
                {
                    HttpContext.Current.Cache[cacheKey] = id;
                }
            }

            currentId = HttpContext.Current.Cache[cacheKey] as int?;
            if (currentId == null)
            {
                HttpContext.Current.Cache[cacheKey] = -1;
            }

            return currentId != -1 ? currentId : null;
        }

        private int? FindUserId(User user)
        {
            if (!user.IsAnonymous && user.Email.HasValue())
            {
                using (DataExplorerSite db = GetOpenConnection())
                {
                    var hash = Util.GravatarHash(user.Email);

                    try
                    {
                        return db.Query<int?>("Site.FindUserId", "SELECT TOP 1 Id FROM Users WHERE EmailHash = @hash ORDER BY Reputation DESC", new { hash }).FirstOrDefault();
                    }
                    catch
                    {
                        // allow this to fail, its not critical
                    }
                }
            }

            return null;
        }


        /// <summary>
        /// Get the sites schema
        /// </summary>
        /// <returns></returns>
        public List<TableInfo> GetTableInfos()
        {
            var tables = new List<TableInfo>();
            TableInfo table = null;

            using (var db = GetOpenConnection())
            {
                var columns = db.Query<ColumnInfo>(@"
                    SELECT
                      TABLE_NAME AS TableName, COLUMN_NAME AS ColumnName, DATA_TYPE AS DataType, CHARACTER_MAXIMUM_LENGTH AS Length
                    FROM
                      INFORMATION_SCHEMA.COLUMNS
                    ORDER BY TABLE_NAME, ORDINAL_POSITION
                ");

                foreach (var column in columns)
                {
                    if (table == null || table.Name != column.TableName)
                    {
                        table = new TableInfo();
                        table.Name = column.TableName;
                        tables.Add(table);
                    }

                    table.ColumnNames.Add(column.ColumnName);
                    table.DataTypes.Add(column.DataType);
                }
            }

            tables.Sort((l, r) =>
                {
                    if (l.Name == "Posts") return -1;
                    if (r.Name == "Posts") return 1;
                    if (l.Name == "Users") return -1;
                    if (r.Name == "Users") return 1;
                    if (l.Name == "Comments") return -1;
                    if (r.Name == "Comments") return 1;
                    if (l.Name == "Badges") return -1;
                    if (r.Name == "Badges") return 1;
                    return l.Name.CompareTo(r.Name);
                }
            );

            return tables;
        }

        #region Nested type: ColumnInfo

        private class ColumnInfo
        {
            private string dataType;

            public string TableName { get; set; }
            public string ColumnName { get; set; }
            public string DataType {
                get
                {
                    return dataType + (Length != null ? " (" + (Length == -1 ? "max" : Length.ToString()) + ")" : "");
                }
                set
                {
                    dataType = value;
                }
            }
            public int? Length { get; set; }
        }

        #endregion

        public SiteInfo SiteInfo { get { return new SiteInfo { Id = Id, Name = Name, Url = Url }; } }

        public object IconProxyUrl { 
            get 
            {
                return "/icon/" + this.Id;
            } 
        }
    }
}